# execl数据提取
import sys
from xlrd import open_workbook # xlrd用于读取xld
from openpyxl import load_workbook, Workbook


# 返回wb对象
def get_wb(execlUrl):
    return load_workbook(execlUrl)

def read(execlUrl):
    # 打开execl文件，获取工作簿对象
    wb = load_workbook(execlUrl)

    # 获取该表所有sheet的名字集合
    sheets = wb.sheetnames

    # 准备最终数据载体
    data = []

    for i in range(len(sheets)):
        sheet = wb[sheets[i]]
        rowNumber = sheet.max_row
        columnNumber = sheet.max_column

        dataOne = {}
        dataOne['sheetName'] = sheets[i]
        dataOne['data'] = []

        for r in range(2, rowNumber + 1):
            dataTwo = []
            for c in range(1, columnNumber + 1):
                dataTwo.append(str(sheet.cell(row=r, column=c).value))
            dataOne['data'].append(dataTwo)

        data.append(dataOne)

    return data

# 读取文件所有数据
def readAll(execlUrl):
    # 打开execl文件，获取工作簿对象
    wb = load_workbook(execlUrl)

    # 获取该表所有sheet的名字集合
    sheets = wb.sheetnames

    # 准备最终数据载体
    data = []

    for i in range(len(sheets)):
        sheet = wb[sheets[i]]
        rowNumber = sheet.max_row
        columnNumber = sheet.max_column

        dataOne = {}
        dataOne['sheetName'] = sheets[i]
        dataOne['data'] = []

        for r in range(1, rowNumber + 1):
            dataTwo = []
            for c in range(1, columnNumber + 1):
                dataTwo.append(str(sheet.cell(row=r, column=c).value) if str(sheet.cell(row=r, column=c).value) != 'None' else '')
            dataOne['data'].append(dataTwo)

        data.append(dataOne)

    return data

def readXLS(url):
    workbook = open_workbook(url)  # 打开xls文件
    sheet = workbook.sheet_by_index(0)  # 根据sheet索引读取sheet中的所有内容
    dataList = []
    for rowIndex in range(sheet.nrows):
        dataList.append(sheet.row_values(rowIndex))
    return dataList

def write(url,dataList):
    with open(url,"a") as file:   #只需要将之前的”w"改为“a"即可，代表追加内容
        for item in dataList:
            file.write(str(item) + " " + "\n")

# 根据坐标插入
def writeByXY(url,x,y,data):
    wb = get_wb(url)
    ws = wb['Sheet1']
    ws.cell(x+1, y+1).value = data
    wb.save(url)
    wb.close()